package cn.ac.cintcm.dataconverge.dataAnalyze.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.jdbc.core.JdbcTemplate;

import cn.ac.cintcm.dataconverge.dao.impl.BaseDao;
import cn.ac.cintcm.dataconverge.dataAnalyze.dao.IAnalyzeDao;
import cn.ac.cintcm.dataconverge.researchsubject.domain.Subject;
import cn.ac.cintcm.dataconverge.util.Constants;

public class AnalyzeDao extends BaseDao implements IAnalyzeDao {
	private static final Logger logger=Logger.getLogger(AnalyzeDao.class);
	private DataSource dataSource;
	
	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List analyzeAge(String org, String zhicheng, String major, String qualification) {
		String hql=this.createHql(org,"","",zhicheng,major,qualification);		
		Session session=this.getHibernateTemplate().getSessionFactory().openSession();
		Query query=session.createQuery(hql);
		List researchers=query.list();
		session.close();
		return researchers;
	}

	public List analyzeZhicheng(String org, String beginAge, String endAge, String major, String qualification) {	
		String hql=this.createHql(org,beginAge,endAge,"",major,qualification);		
		Session session=this.getHibernateTemplate().getSessionFactory().openSession();
		Query query=session.createQuery(hql);
		List researchers=query.list();
		session.close();		
		return researchers;
	}

	public List  analyzeQualification(String org, String beginAge, String endAge, String zhicheng, String major) {	
		String hql=this.createHql(org,beginAge,endAge,zhicheng,major,"");		
		Session session=this.getHibernateTemplate().getSessionFactory().openSession();
		Query query=session.createQuery(hql);
		List researchers=query.list();
		session.close();		
		return researchers;
	}

	private String createHql(String org, String beginAge, String endAge, String zhicheng,String major, String qualification){
		String hql="from Researcher as t";
		boolean bool=false;
		if(org!=null && !org.equals("")){
			hql+=" where t.org.id="+Integer.parseInt(org);
			bool=true;
		}
		Date now=new Date(System.currentTimeMillis());		
		int thisYear=now.getYear()+1900;
		if(beginAge!=null && !beginAge.equals("")){
			int temp=thisYear-Integer.parseInt(beginAge);
			if(bool){
				hql+=" and t.birthday<=TO_DATE('"+temp+"-12-31','YYYY-MM-DD')";
			}
			else{
				hql+=" where t.birthday<=TO_DATE('"+temp+"-12-31','YYYY-MM-DD')";
				bool=true;
			}
		}
		if(endAge!=null && !endAge.equals("")){
			int temp=thisYear-Integer.parseInt(endAge);
			if(bool){
				hql+=" and t.birthday>=TO_DATE('"+temp+"-01-01','YYYY-MM-DD')";
			}
			else{
				hql+=" where t.birthday>=TO_DATE('"+temp+"-01-01','YYYY-MM-DD')";
				bool=true;
			}
		}
		if(major!=null &&!major.equals("")){
			if(bool){
				hql+=" and t.major='"+major+"'";
			}
			else{
				hql+=" where t.major='"+major+"'";
				bool=true;
			}
		}
		if(zhicheng!=null &&!zhicheng.equals("")){
			if(bool){
				hql+=" and t.zhicheng='"+zhicheng+"'";
			}
			else{
				hql+=" where t.zhicheng='"+zhicheng+"'";
				bool=true;
			}
		}	
		if(qualification!=null &&!qualification.equals("")){
			if(bool){
				hql+=" and t.qualification='"+qualification+"'";
			}
			else{
				hql+=" where t.qualification='"+qualification+"'";
				bool=true;
			}
		}
		logger.info(hql);
		return hql;
	}
/*	
	private List getQueryResults(String hql){
		Session session=this.getHibernateTemplate().getSessionFactory().openSession();
		Query query=session.createQuery(hql);
		List list=query.list();
		session.close();
		return list;
	}
	
	private String converge(List list){
		String ids="";
		ids="(";
		if(list.size()>0){
			for(int i=0;i<list.size()-1;i++){
				ids+="'"+list.get(i).toString()+"',";
			}
			ids+="'"+list.get(list.size()-1).toString()+"'";
		}
		else{
			ids+="''";
		}
		ids+=")";
		return ids;
	}
	
	private List getSubjectIdsFromOrg(String org){
		List subjectIds=new ArrayList();
		JdbcTemplate jt=new JdbcTemplate(dataSource);
		String sql="select subjectId from "+Constants.subject_org_table+" where orgId="+Integer.parseInt(org);
		List results=jt.queryForList(sql);
		Iterator iter=results.iterator();
		while(iter.hasNext()){
			Map map=(Map)iter.next();
			String id=map.get("subjectId").toString();
			subjectIds.add(id);
		}				
		return subjectIds;
	}
	*/
	public List getResearchersFromSubjects(List subjects){
		List researcherIds=new ArrayList();
		String sql="";
		if(subjects!=null &&subjects.size()>0){
			Subject subject=(Subject)subjects.get(0);
			sql="select researcherId from "+Constants.subject_researcher_table+" where subjectId="+subject.getId();
			for(int i=1;i<subjects.size();i++){
				subject=(Subject)subjects.get(i);
				sql+=" or subjectId="+subject.getId();
			}
			//logger.info(sql);
			JdbcTemplate jt=new JdbcTemplate(dataSource);
			List results=jt.queryForList(sql);
			Iterator iter=results.iterator();
			while(iter.hasNext()){
				Map map=(Map)iter.next();
				if(map.get("researcherId")!=null && !map.get("researcherId").toString().equals("")){
					String id=map.get("researcherId").toString();
					researcherIds.add(id);
				}			
			}		
		}
		return researcherIds;
	}

	

}
